package com.xdhl.fu.member.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xdhl.fu.member.domain.MemberBasic;
import com.xdhl.fu.member.domain.vo.TotalMemberVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 会员基本信息Mapper接口
 *
 * @author 朱力维
 * @time 2022/5/27 16:43
 */
@Mapper
public interface MemberBasicMapper extends BaseMapper<MemberBasic> {

    /**
     * 查询n月内活跃度比较高的用户信息
     * 活跃度默认按照登录次数计算
     *
     * @param month 月数
     * @return 活跃度比较高的用户信息
     */
    @Select("select\n" +
            "mb.data_id,\n" +
            "mb.m_number,\n" +
            "mb.first_name_cn,\n" +
            "mb.last_name_cn,\n" +
            "mb.telephone_number \n" +
            "from\n" +
            "member_basic as mb\n" +
            "inner join ( select lg.member_data_id, count( member_data_id ) as co from member_logininfor as lg where lg.create_time >= date_sub(now(), interval #{month} month) group by lg.member_data_id order by co desc ) as ds on mb.data_id = ds.member_data_id")
    List<MemberBasic> listActive(Integer month);

    @Select("SELECT\n" +
            "mb.occupation_class as occupation_class,\n" +
            "count( mb.data_id ) as count\n" +
            "FROM\n" +
            "member_basic mb \n" +
            "where\n" +
            "date_format( mb.create_time, '%Y-%m-%d' ) = date_format( now(), '%Y-%m-%d' ) \n" +
            "group by\n" +
            "mb.occupation_class")
    List<TotalMemberVO> countGroupRegisteredToday();

    @Select("select\n" +
            "date_format( ml.create_time, '%Y-%m-%d' ) as record_date,\n" +
            "count( data_id ) as count \n" +
            "from\n" +
            "member_logininfor ml \n" +
            "where\n" +
            "date_format( date_sub( now(), interval 7 day ), '%Y-%m-%d' ) <= date_format( ml.create_time, '%Y-%m-%d' ) \n" +
            "group by\n" +
            "record_date")
    List<TotalMemberVO> countMemberActive7Days();

}
